package com.hqyj.bigdata.hiveproject.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hqyj.bigdata.hiveproject.entity.TopTenCatalogInfo;
import com.hqyj.bigdata.hiveproject.entity.TopTenCatalogVideoInfo;
import com.hqyj.bigdata.hiveproject.mapper.TopTenCatalogVideoInfoMapper;
import com.hqyj.bigdata.hiveproject.service.TopTenCatalogInfoService;
import com.hqyj.bigdata.hiveproject.service.TopTenCatalogVideoInfoService;
import com.hqyj.bigdata.hiveproject.util.HiveConnection;
import com.hqyj.bigdata.hiveproject.view.TopTenCatalogVideoInfoView;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.annotation.Resource;
import javax.transaction.Transactional;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Service
public class TopTenCatalogVideoInfoServiceImpl extends ServiceImpl<TopTenCatalogVideoInfoMapper, TopTenCatalogVideoInfo> implements TopTenCatalogVideoInfoService {

    @Resource
    private TopTenCatalogVideoInfoMapper topTenCatalogVideoInfoMapper;

    @Transactional(rollbackOn = Exception.class)
    @Override
    public boolean initTopTenCatalogVideoInfo() throws Exception {
        String sql = "select\n" +
                "   categoryId,\n" +
                "   videoId,\n" +
                "   views\n" +
                "from \n" +
                "   (select\n" +
                "   categoryId,\n" +
                "   videoId,\n" +
                "   views,\n" +
                "   rank() over(partition by categoryId order by views desc) rk\n" +
                "from\n" +
                "   gulivideo_category)t1\n" +
                "where rk<=10";

        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<TopTenCatalogVideoInfo> topTenCatalogVideoInfos = new ArrayList<TopTenCatalogVideoInfo>();

        try {
            pstm = HiveConnection.getConnection().prepareStatement(sql);
            //boolean flag = pstm.execute(sql);

            rs = pstm.executeQuery(); //得到数据集
            while (rs.next()) {
                //System.out.println(rs.getString("videoid") + "," + rs.getLong("views"));
                TopTenCatalogVideoInfo topTenCatalogVideoInfo = TopTenCatalogVideoInfo.builder().categoryId(rs.getString("categoryId")).videoId(rs.getString("videoId")).views(rs.getLong("views")).build();
                topTenCatalogVideoInfos.add(topTenCatalogVideoInfo);
            }

            return super.saveBatch(topTenCatalogVideoInfos);
        } catch (Exception ex) {
            ex.printStackTrace();
            // 事务的回滚
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }

                if (pstm != null) {
                    pstm.close();
                    pstm = null;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    @Override
    public List<TopTenCatalogVideoInfo> queryTopTenCatalogVideoInfoList() throws Exception {
        return super.list(null);
    }

    @Override
    public TopTenCatalogVideoInfoView queryTopTenCatalogVideoInfoView() throws Exception {
        TopTenCatalogVideoInfoView topTenCatalogVideoInfoView = new TopTenCatalogVideoInfoView();
        try{
            List<String> catalogList = topTenCatalogVideoInfoMapper.queryCatalogList();
            topTenCatalogVideoInfoView.setCatalogList(catalogList);

            List<List<Long>> catalogVideoViewList = new ArrayList<List<Long>>();
            for(String catalogId : catalogList){
                List<Long> tempList = topTenCatalogVideoInfoMapper.queryVideoViewsListByCatalogId(catalogId);
                catalogVideoViewList.add(tempList);
            }

            topTenCatalogVideoInfoView.setCatalogVideoViewsList(catalogVideoViewList);

            return topTenCatalogVideoInfoView;
        }catch (Exception ex){
            ex.printStackTrace();
            return null;
        }
    }
}
